package com.android.nextBus.DB;

import java.util.ArrayList;

/**
 * This class is used to define our SQLite tables and columns We define all our
 * tables and columns here as static strings so we can call the appropriate
 * method to create all the tables in our SQLite database
 * 
 */
public class SQLiteTablesAndColumns {

	// First we define our table name here as a string to be used in our
	// create string
	public static final String STOP_TIMES_TABLE = "stop_times";
	public static final String STOPS_TABLE = "stops";
	public static final String TRIPS_TABLE = "trips";
	public static final String ROUTES_TABLE = "routes";
	public static final String SHAPES_TABLE = "shapes";
	public static final String FAVORITES_TABLE = "favorites";
	public static final String ROUTES_STOPS_TABLE = "routes_stops";
	public static final String CREDENTIALS_TABLE = "credentials";
	public static final String HOLIDAY_TABLE = "holiday";
	

	// now define the all the Text columns here
	public static final String PASSWORD_COLUMN = "password";
	public static final String NAME_COLUMN = "name";
	public static final String EMAIL_COLUMN = "email_address";
	public static final String COUNTRY_COLUMN = "country";
	public static final String ADDRESS_COLUMN = "address";
	public static final String ZIP_CODE_COLUMN = "zip_code";
	public static final String BUS_PASS_COLUMN = "bus_pass";
	public static final String FIRST_TIME_FLAG_COLUMN = "first_time_flag";

	
	public static final String ROUTE_ID_COLUMN = "route_id";
	public static final String ROUTE_LONG_NAME_COLUMN = "route_long_name";
	public static final String STOP_NAME_COLUMN = "stop_name";
	public static final String STOP_DESC_COLUMN = "stop_desc";
	public static final String TRIP_ID_COLUMN = "trip_id";
	public static final String TRIP_HEADSIGN_COLUMN = "trip_headsign";
	
	// now define the the Integer value columns here
	public static final String PHONE_NUMBER_COLUMN = "phonenumber";
	
	public static final String STOP_ID_COLUMN = "stop_id";
	public static final String SHAPE_ID_COLUMN = "shape_id";
	public static final String STOP_LAT_COLUMN = "stop_lat";
	public static final String STOP_LON_COLUMN = "stop_lon";
	public static final String ROUTE_SHORT_NAME_COLUMN = "route_short_name";
	public static final String SHAPE_POINT_SEQUENCE_COLUMN = "shape_point_sequence";
	public static final String SHAPE_POINT_LAT_COLUMN = "shape_point_lat";
	public static final String SHAPE_POINT_LON_COLUMN = "shape_point_lon";
	public static final String DEPARTURE_TIME_COLUMN = "departure_time";
	public static final String ACCESSED_DATE_COLUMN = "last_accessed_date";
	public static final String UPDATED_DATE_COLUMN = "last_updated_date";
	public static final String HOLIDAY_DATE_COLUMN = "holiday_date";

	// next we will place all the Text columns, Integer columns, foreign key
	// columns, and
	// foreign key reference tables array - Into there own string array.

	//CREDENTIALS_TABLE arrays
	public static final String[] CREDENTIALS_TABLE_TEXT_COLUMN_NAMES_ARRAY = {
		PASSWORD_COLUMN, 
		NAME_COLUMN,
		EMAIL_COLUMN,
		COUNTRY_COLUMN,
		ADDRESS_COLUMN,
		BUS_PASS_COLUMN
	};
	
	public static final String[] CREDENTIALS_TABLE_INTEGER_COLUMN_NAMES_ARRAY = {
		FIRST_TIME_FLAG_COLUMN,
		ZIP_CODE_COLUMN
	};
	
	public static final String[] CREDENTIALS_TABLE_FK_COLUMN_NAMES_ARRAY = {};
	
	public static final String[] CREDENTIALS_TABLE_FK_REFERENCES_TABLE_ARRAY = {};
	
	// HOLIDAY_TABLE arrays
	public static final String[] HOLIDAY_TABLE_TEXT_COLUMN_NAMES_ARRAY = {
	
	};
	
	public static final String[] HOLIDAY_TABLE_INTEGER_COLUMN_NAMES_ARRAY = {
		HOLIDAY_DATE_COLUMN
	};
	
	public static final String[] HOLIDAY_TABLE_FK_COLUMN_NAMES_ARRAY = {};
	
	public static final String[] HOLIDAY_TABLE_FK_REFERENCES_TABLE_ARRAY = {};
	
	// STOP_TIMES_TABLE ARRAYS
	public static final String[] STOP_TIMES_TABLE_TEXT_COLUMN_NAMES_ARRAY = {};
	
	public static final String[] STOP_TIMES_TABLE_INTEGER_COLUMN_NAMES_ARRAY = {
		STOP_ID_COLUMN, DEPARTURE_TIME_COLUMN, ACCESSED_DATE_COLUMN, 
		UPDATED_DATE_COLUMN, TRIP_ID_COLUMN
	};
	
	public static final String[] STOP_TIMES_TABLE_FK_COLUMN_NAMES_ARRAY = {
		STOP_ID_COLUMN, TRIP_ID_COLUMN
	};
	public static final String[] STOP_TIMES_TABLE_FK_REFERENCES_TABLE_ARRAY = {
		STOPS_TABLE, TRIPS_TABLE
	};
	
	
	// TRIPS_TABLE ARRAYS
	public static final String[] TRIPS_TABLE_TEXT_COLUMN_NAMES_ARRAY = {
		ROUTE_ID_COLUMN, TRIP_HEADSIGN_COLUMN
	};
	
	public static final String[] TRIPS_TABLE_INTEGER_COLUMN_NAMES_ARRAY = {
		SHAPE_ID_COLUMN,ACCESSED_DATE_COLUMN, UPDATED_DATE_COLUMN,TRIP_ID_COLUMN
	};
	
	public static final String[] TRIPS_TABLE_FK_COLUMN_NAMES_ARRAY = {
		SHAPE_ID_COLUMN, ROUTE_ID_COLUMN
	};
	
	public static final String[] TRIPS_TABLE_FK_REFERENCES_TABLE_ARRAY = {
		SHAPES_TABLE, ROUTES_TABLE
	};
	
	
	// STOPS_TABLE ARRAYS
	public static final String[] STOPS_TABLE_TEXT_COLUMN_NAMES_ARRAY = {
		STOP_NAME_COLUMN, STOP_DESC_COLUMN
	};
	
	public static final String[] STOPS_TABLE_INTEGER_COLUMN_NAMES_ARRAY = {
		STOP_ID_COLUMN, STOP_LAT_COLUMN, STOP_LON_COLUMN, 
		ACCESSED_DATE_COLUMN, UPDATED_DATE_COLUMN
	};
	
	public static final String[] STOPS_TABLE_FK_COLUMN_NAMES_ARRAY = {};
	
	public static final String[] STOPS_TABLE_FK_REFERENCES_TABLE_ARRAY = {};
	
	
	// ROUTES_TABLE ARRAYS
	public static final String[] ROUTES_TABLE_TEXT_COLUMN_NAMES_ARRAY = {
		ROUTE_ID_COLUMN, ROUTE_SHORT_NAME_COLUMN, ROUTE_LONG_NAME_COLUMN
	};
	
	public static final String[] ROUTES_TABLE_INTEGER_COLUMN_NAMES_ARRAY = {
		ACCESSED_DATE_COLUMN, UPDATED_DATE_COLUMN
	};
	
	public static final String[] ROUTES_TABLE_FK_COLUMN_NAMES_ARRAY = {};
	
	public static final String[] ROUTES_TABLE_FK_REFERENCES_TABLE_ARRAY = {};
	
	
	// SHAPES_TABLE ARRAYS
	public static final String[] SHAPES_TABLE_TEXT_COLUMN_NAMES_ARRAY = {};
	
	public static final String[] SHAPES_TABLE_INTEGER_COLUMN_NAMES_ARRAY = {
		SHAPE_ID_COLUMN, SHAPE_POINT_LAT_COLUMN, SHAPE_POINT_LON_COLUMN, 
		SHAPE_POINT_SEQUENCE_COLUMN, ACCESSED_DATE_COLUMN, 
		UPDATED_DATE_COLUMN
	};
	
	public static final String[] SHAPES_TABLE_FK_COLUMN_NAMES_ARRAY = {};
	
	public static final String[] SHAPES_TABLE_FK_REFERENCES_TABLE_ARRAY = {};
	
	
	// FAVORITES_TABLE ARRAYS
	public static final String[] FAVORITES_TABLE_TEXT_COLUMN_NAMES_ARRAY = {};
		
	public static final String[] FAVORITES_TABLE_INTEGER_COLUMN_NAMES_ARRAY = {
		STOP_ID_COLUMN, UPDATED_DATE_COLUMN, ACCESSED_DATE_COLUMN
	};
		
	public static final String[] FAVORITES_TABLE_FK_COLUMN_NAMES_ARRAY = {
		STOP_ID_COLUMN
	};
	
	public static final String[] FAVORITES_TABLE_FK_REFERENCES_TABLE_ARRAY = {
		STOPS_TABLE
	};
		
	// ROUTES_STOPS_TABLE ARRAYS
	public static final String[] ROUTES_STOPS_TABLE_TEXT_COLUMN_NAMES_ARRAY = {};
	
	public static final String[] ROUTES_STOPS_TABLE_INTEGER_COLUMN_NAMES_ARRAY = {
		ROUTE_ID_COLUMN, STOP_ID_COLUMN,UPDATED_DATE_COLUMN, ACCESSED_DATE_COLUMN
	};
	
	public static final String[] ROUTES_STOPS_TABLE_FK_COLUMN_NAMES_ARRAY = {
		ROUTE_ID_COLUMN, STOP_ID_COLUMN
	};
	
	public static final String[] ROUTES_STOPS_TABLE_FK_REFERENCES_TABLE_ARRAY = {
		ROUTES_TABLE, STOPS_TABLE
	};
	
	
	// now declare an ArrayList called MASTER_TABLE_LIST that has SQLite table
	// objects. We will use this list to construct our create strings 
	public static final ArrayList<SqliteTable>  MASTER_TABLE_LIST;

	
	// static block to initialize a master table list
	// the master table list can be accessed to give details about all table
	// schemas
	static{
			
		MASTER_TABLE_LIST = new ArrayList<SqliteTable>();
		
		MASTER_TABLE_LIST.add(new SqliteTable(CREDENTIALS_TABLE,
				CREDENTIALS_TABLE_TEXT_COLUMN_NAMES_ARRAY,
				CREDENTIALS_TABLE_INTEGER_COLUMN_NAMES_ARRAY,
				CREDENTIALS_TABLE_FK_COLUMN_NAMES_ARRAY,
				CREDENTIALS_TABLE_FK_REFERENCES_TABLE_ARRAY));
		
		MASTER_TABLE_LIST.add(new SqliteTable(ROUTES_TABLE,
				ROUTES_TABLE_TEXT_COLUMN_NAMES_ARRAY,
				ROUTES_TABLE_INTEGER_COLUMN_NAMES_ARRAY,
				ROUTES_TABLE_FK_COLUMN_NAMES_ARRAY,
				ROUTES_TABLE_FK_REFERENCES_TABLE_ARRAY));
		
		MASTER_TABLE_LIST.add(new SqliteTable(HOLIDAY_TABLE,
				HOLIDAY_TABLE_TEXT_COLUMN_NAMES_ARRAY,
				HOLIDAY_TABLE_INTEGER_COLUMN_NAMES_ARRAY,
				HOLIDAY_TABLE_FK_COLUMN_NAMES_ARRAY,
				HOLIDAY_TABLE_FK_REFERENCES_TABLE_ARRAY));
		
		MASTER_TABLE_LIST.add(new SqliteTable(STOPS_TABLE,
				STOPS_TABLE_TEXT_COLUMN_NAMES_ARRAY,
				STOPS_TABLE_INTEGER_COLUMN_NAMES_ARRAY,
				STOPS_TABLE_FK_COLUMN_NAMES_ARRAY,
				STOPS_TABLE_FK_REFERENCES_TABLE_ARRAY));
		
		MASTER_TABLE_LIST.add(new SqliteTable(STOP_TIMES_TABLE,
				STOP_TIMES_TABLE_TEXT_COLUMN_NAMES_ARRAY,
				STOP_TIMES_TABLE_INTEGER_COLUMN_NAMES_ARRAY,
				STOP_TIMES_TABLE_FK_COLUMN_NAMES_ARRAY,
				STOP_TIMES_TABLE_FK_REFERENCES_TABLE_ARRAY));
		
		MASTER_TABLE_LIST.add(new SqliteTable(SHAPES_TABLE,
				SHAPES_TABLE_TEXT_COLUMN_NAMES_ARRAY,
				SHAPES_TABLE_INTEGER_COLUMN_NAMES_ARRAY,
				SHAPES_TABLE_FK_COLUMN_NAMES_ARRAY,
				SHAPES_TABLE_FK_REFERENCES_TABLE_ARRAY));
		
		MASTER_TABLE_LIST.add(new SqliteTable(TRIPS_TABLE,
				TRIPS_TABLE_TEXT_COLUMN_NAMES_ARRAY,
				TRIPS_TABLE_INTEGER_COLUMN_NAMES_ARRAY,
				TRIPS_TABLE_FK_COLUMN_NAMES_ARRAY,
				TRIPS_TABLE_FK_REFERENCES_TABLE_ARRAY));
		
		MASTER_TABLE_LIST.add(new SqliteTable(FAVORITES_TABLE,
				FAVORITES_TABLE_TEXT_COLUMN_NAMES_ARRAY,
				FAVORITES_TABLE_INTEGER_COLUMN_NAMES_ARRAY,
				FAVORITES_TABLE_FK_COLUMN_NAMES_ARRAY,
				FAVORITES_TABLE_FK_REFERENCES_TABLE_ARRAY));
		
		MASTER_TABLE_LIST.add(new SqliteTable(ROUTES_STOPS_TABLE,
				ROUTES_STOPS_TABLE_TEXT_COLUMN_NAMES_ARRAY,
				ROUTES_STOPS_TABLE_INTEGER_COLUMN_NAMES_ARRAY,
				ROUTES_STOPS_TABLE_FK_COLUMN_NAMES_ARRAY,
				ROUTES_STOPS_TABLE_FK_REFERENCES_TABLE_ARRAY));
	}
	
}
